alter table LvReo_MasterList
add IsDeleted bit not null default(0)
go
ALTER PROCEDURE [dbo].[deleteLvreo_MasterList]
(
    @PropertyID int
)

AS
BEGIN
	set nocount on
	
	update LvReo_MasterList
	set IsDeleted = 1
	where PropertyId = @PropertyID
END
go
create view vwLvReo_MasterList
as
select *
from LvReo_MasterList
where IsDeleted = 0
go
ALTER PROCEDURE [dbo].[getAvailableInvestments]
	@currentUserId int,
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '' --Zip	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    declare @userSubRole char(1)
	set @userSubRole = (select [SubRole] from UserAccounts where UserId = @currentUserId)
	
	if @userSubRole = 'L'
	begin
        SELECT m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
               m.PropertyLocation, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
               p.LotSize, p.ConstructYear, p.LandUse, p.TotalSqft, p.GarageSqft,p.Stories, p.Bedrooms, p.Bathrooms,
               p.Pool, p.Spa, p.AssessorDesc, m.Phase,
               Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source,
               cast(case when pr.UserId IS null then 0 else 1 end as bit) as Requested
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
				  AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
				  AND isnull(ZipCode, '') LIKE '%'+@Zip+'%'
				  and InvestorId is null)  AS m 
         LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
         left join PropertyRequests pr on m.Apn = pr.Apn and pr.UserId = @currentUserId
        where (InvestorId is null) and (ListingStatus = 'Available')
		ORDER BY
			case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end
	end
	else if @userSubRole = 'S'
	begin
		SELECT m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
			   m.PropertyLocation, m.LotSize, m.ConstructYear, m.LandUse, m.TotalSqft, m.GarageSqft,m.Stories, m.Bedrooms, 
			   m.Bathrooms, m.Pool, m.Spa, m.AssessorDesc, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, 
			   m.MaxBid, m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
			   m.Phase,
			   Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source,
			   cast(case when pr.UserId IS null then 0 else 1 end as bit) as Requested
		FROM Sdreo_MasterList m
			left join PropertyRequests pr on m.Apn = pr.Apn and pr.UserId = @currentUserId
		WHERE m.Apn LIKE '%'+@Apn+'%' 
		  AND PropertyLocation LIKE '%'+@PropLocation+'%'
		  AND ZipCode LIKE '%'+@Zip+'%'
		  and InvestorId is null
		  and ListingStatus = 'Available'
		ORDER BY
			case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end
	end
END
go
ALTER PROCEDURE [dbo].[getDashboard1]
(
    @InvestorId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT PropertyId, PropertyLocation,
		CONVERT(INT, EstimatedResale) AS EstimatedResale, 
		CONVERT(INT, NetSheet_SellingPrice) AS ActualResale,
		CONVERT(INT, RehabEstimate) AS EstimatedRehab,
		CONVERT(INT, RehabMiscCosts + RehabEstimate) AS ActualRehab
	FROM vwLvReo_MasterList
	WHERE (InvestorId = @InvestorId or (@InvestorId is null))
		and (CONVERT(INT, EstimatedResale) <> 0)
		and (CONVERT(INT, NetSheet_SellingPrice) <> 0)
		and (CONVERT(INT, RehabEstimate) <> 0)
		and (CONVERT(INT, RehabMiscCosts + RehabEstimate) <> 0)

END
go
ALTER PROCEDURE [dbo].[getDashboard2]
(
    @InvestorId INT,
    @TypeView INT    -- 3 - by year, 2 - by qt, 1 - by month
    ,@propertyType varchar(3) = null
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @StartDate DATETIME, @MinDate DATETIME
	SET @StartDate = GETDATE()
			
	CREATE TABLE #tmpPeriod
	(
		IdRow INT IDENTITY(1, 1),
		IdYear INT,
		IdSub INT,
		CountProp INT
	)
		
	SELECT @MinDate = MIN(cast(RECORDED_F as datetime))
	FROM vwLvReo_MasterList
	WHERE (InvestorId = @InvestorId) and ([Type] = @propertyType or @propertyType is null)
		and (isdate(RECORDED_F) = 1)
	
	IF (@TypeView = 3)
	BEGIN
		WHILE YEAR(@StartDate) >= YEAR(@MinDate)
		BEGIN
			INSERT INTO #tmpPeriod
			(IdYear)
			VALUES(YEAR(@StartDate))
			
			SET @StartDate = DATEADD(YEAR, -1, @StartDate)
		END	
		
		UPDATE #tmpPeriod
		SET CountProp = vw.Cnt
		FROM (SELECT YEAR(cast(RECORDED_F as datetime)) AS PY, COUNT(*) AS Cnt
				FROM vwLvReo_MasterList
				WHERE (InvestorId = @InvestorId) and ([Type] = @propertyType or @propertyType is null) and (isdate(RECORDED_F) = 1)
				GROUP BY YEAR(cast(RECORDED_F as datetime))) AS vw
		WHERE #tmpPeriod.IdYear = vw.PY
		
	END 
	ELSE IF (@TypeView = 2)
	BEGIN
		WHILE (YEAR(@StartDate) > YEAR(@MinDate)) OR 
				((YEAR(@StartDate) = YEAR(@MinDate)) AND (DATEPART(QUARTER, @StartDate) >= DATEPART(QUARTER, @MinDate)))
		BEGIN
			INSERT INTO #tmpPeriod
			(IdYear, IdSub)
			VALUES(YEAR(@StartDate), DATEPART(QUARTER, @StartDate))
			
			SET @StartDate = DATEADD(QUARTER, -1, @StartDate)
		END	
		
		UPDATE #tmpPeriod
		SET CountProp = vw.Cnt
		FROM (SELECT YEAR(cast(RECORDED_F as datetime)) AS PY, DATEPART(QUARTER, cast(RECORDED_F as datetime)) AS PQ, COUNT(*) AS Cnt
				FROM vwLvReo_MasterList
				WHERE (InvestorId = @InvestorId) and ([Type] = @propertyType or @propertyType is null) and (isdate(RECORDED_F) = 1)
				GROUP BY YEAR(cast(RECORDED_F as datetime)), DATEPART(QUARTER, cast(RECORDED_F as datetime))) AS vw
		WHERE #tmpPeriod.IdYear = vw.PY
			AND #tmpPeriod.IdSub = vw.PQ
	END	
	ELSE IF (@TypeView = 1)
	BEGIN
		WHILE (YEAR(@StartDate) > YEAR(@MinDate)) OR 
				((YEAR(@StartDate) = YEAR(@MinDate)) AND (DATEPART(MONTH, @StartDate) >= DATEPART(MONTH, @MinDate)))
		BEGIN
			INSERT INTO #tmpPeriod
			(IdYear, IdSub)
			VALUES(YEAR(@StartDate), DATEPART(MONTH, @StartDate))
			
			SET @StartDate = DATEADD(MONTH, -1, @StartDate)
		END		
		
		UPDATE #tmpPeriod
		SET CountProp = vw.Cnt
		FROM (SELECT YEAR(cast(RECORDED_F as datetime)) AS PY, DATEPART(MONTH, cast(RECORDED_F as datetime)) AS PM, COUNT(*) AS Cnt
				FROM vwLvReo_MasterList
				WHERE (InvestorId = @InvestorId) and ([Type] = @propertyType or @propertyType is null) and (isdate(RECORDED_F) = 1)
				GROUP BY YEAR(cast(RECORDED_F as datetime)), DATEPART(MONTH, cast(RECORDED_F as datetime))) AS vw
		WHERE #tmpPeriod.IdYear = vw.PY
			AND #tmpPeriod.IdSub = vw.PM
	END
	
	SELECT IdRow, IdYear, IdSub, CountProp
	FROM #tmpPeriod
	ORDER BY IdRow desc
	
	DROP TABLE #tmpPeriod
END
go
ALTER PROCEDURE [dbo].[getDashboard3]
(
    @InvestorId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @StartDate DATETIME, @MinDate DATETIME
	SET @StartDate = GETDATE()
	
	IF (@InvestorId IS NOT NULL)
		SELECT @MinDate = MIN(Recorded_B)
		FROM vwLvReo_MasterList
		WHERE (InvestorId = @InvestorId)
			AND (Recorded_B IS NOT NULL)
	ELSE
		SELECT @MinDate = MIN(Recorded_B)
		FROM vwLvReo_MasterList
		WHERE (Recorded_B IS NOT NULL)
	
	
	CREATE TABLE #tmpPeriod
	(
		IdRow INT IDENTITY(1, 1),
		IdYear INT,
		IdSub INT,
		
		AvgSellingPrice INT,
		AvgPurchasePrice INT,
		AvgRehabCosts INT,
		AvgNetProfit INT,
		
		AvgSellingPriceProc INT,
		AvgPurchasePriceProc INT,
		AvgRehabCostsProc INT,
		AvgNetProfitProc INT
	)
	
	WHILE (YEAR(@StartDate) > YEAR(@MinDate)) OR 
		((YEAR(@StartDate) = YEAR(@MinDate)) AND (DATEPART(MONTH, @StartDate) >= DATEPART(MONTH, @MinDate)))
	BEGIN
		INSERT INTO #tmpPeriod
		(IdYear, IdSub)
		VALUES(YEAR(@StartDate), DATEPART(MONTH, @StartDate))
			
		SET @StartDate = DATEADD(MONTH, -1, @StartDate)
	END	
	
	IF (@InvestorId IS NOT NULL)
		UPDATE #tmpPeriod
		SET AvgSellingPrice = vw.AvgSellingPrice,
			AvgPurchasePrice = vw.AvgPurchasePrice,
			--AvgRehabCosts = vw.AvgRehabCosts,
			AvgRehabCosts = vw.AvgSellingPrice - (vw.AvgPurchasePrice + vw.AvgNetProfit),
			AvgNetProfit = vw.AvgNetProfit
		FROM (SELECT YEAR(Recorded_B) AS IdYear, MONTH(Recorded_B) AS IdMonth,
				AVG(NetSheet_SellingPrice) AS AvgSellingPrice,
				AVG(dbo.fnCastAsMoney(PurchasePrice)) AS AvgPurchasePrice,
				AVG(dbo.fnCastAsMoney(RehabCosts)) AS AvgRehabCosts,
				AVG(NetProfit) AS AvgNetProfit
			FROM vwLvReo_MasterList
			WHERE (InvestorId = @InvestorId)
				AND (Recorded_B IS NOT NULL)
			GROUP BY YEAR(Recorded_B), MONTH(Recorded_B)) AS vw
		WHERE (#tmpPeriod.IdYear = vw.IdYear)
			AND (#tmpPeriod.IdSub = vw.IdMonth)
	ELSE
		UPDATE #tmpPeriod
		SET AvgSellingPrice = vw.AvgSellingPrice,
			AvgPurchasePrice = vw.AvgPurchasePrice,
			--AvgRehabCosts = vw.AvgRehabCosts,
			AvgRehabCosts = vw.AvgSellingPrice - (vw.AvgPurchasePrice + vw.AvgNetProfit),
			AvgNetProfit = vw.AvgNetProfit
		FROM (SELECT YEAR(Recorded_B) AS IdYear, MONTH(Recorded_B) AS IdMonth,
				AVG(NetSheet_SellingPrice) AS AvgSellingPrice,
				AVG(dbo.fnCastAsMoney(PurchasePrice)) AS AvgPurchasePrice,
				AVG(dbo.fnCastAsMoney(RehabCosts)) AS AvgRehabCosts,
				AVG(NetProfit) AS AvgNetProfit
			FROM vwLvReo_MasterList
			WHERE (Recorded_B IS NOT NULL)
			GROUP BY YEAR(Recorded_B), MONTH(Recorded_B)) AS vw
		WHERE (#tmpPeriod.IdYear = vw.IdYear)
			AND (#tmpPeriod.IdSub = vw.IdMonth)
	
		
	/*(sell price/(purchase price + cost of Operation))
		(purchase price/(purchase price + cost of Operation))
		(cost of operation/(purchase price + cost of Operation))
		(net profit/(purchase price + cost of Operation))*/
	UPDATE #tmpPeriod
	SET AvgSellingPriceProc = (ISNULL(AvgSellingPrice, 0) * 100) / (ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0)),
		AvgPurchasePriceProc = (ISNULL(AvgPurchasePrice, 0) * 100) / (ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0)),
		AvgRehabCostsProc = (ISNULL(AvgRehabCosts, 0) * 100) / (ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0)),
		AvgNetProfitProc = (ISNULL(AvgNetProfit, 0) * 100) / (ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0))
	WHERE ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0) <> 0
	
	SELECT IdRow, IdYear, IdSub,
		AvgSellingPrice, AvgPurchasePrice, AvgRehabCosts, AvgNetProfit, 
		case when (AvgPurchasePrice is null) and (AvgRehabCosts is null) then null 
		else ISNULL(AvgPurchasePrice, 0) + ISNULL(AvgRehabCosts, 0) end AS AvgPurchasePlusCostOperation,
		AvgSellingPriceProc, AvgPurchasePriceProc, AvgRehabCostsProc, AvgNetProfitProc
	FROM #tmpPeriod
	ORDER BY IdYear, IdSub
		
	DROP TABLE #tmpPeriod
END
go
ALTER PROCEDURE [dbo].[getLvreo_List]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0
)

AS

BEGIN
	declare @percentT1 money
	declare @percentAA1 money
	set @percentT1 = (select Value from Config where [Key] = 'PercentT1')
	set @percentAA1 = (select Value from Config where [Key] = 'PercentAA1')
	
	declare @maxDate datetime
	SET @maxDate = '99991231'

	SELECT 
			case when dbo.fnCastAsDate(m.CloseDate) is null then 0 else 1 end as PrimaryOrder,
			case when dbo.fnCastAsDate(m.CloseDate) is null then PropertyLocation else convert(varchar(10), RECORDED_F, 112) end as SecondaryOrder,
			
			-- ALL
			m.PropertyLocation, ua.Fname + ' ' + ua.LName as InvestorName, m.Apn, m.ZipCode, 
			
			--1
			m.ListingStatus, Source, dbo.fnCastAsDate(m.DateRecorded) as DateRecorded, m.EscrowCompany, p.AssessorDesc,
			m.Hoa, p.LotSize, m.PrelistStatus, m.MiscNotes, p.LandUse, m.Phase,
			
			--2
			m.EMD, m.Reimb, m.Insur, m.Assmts, dbo.fnCastAsDate(m.StartDate) as StartDate, 
			ClosingCost = cast(round(dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100, 0) as int), 
			-- PurchasePrice + ClosingCost + RehabCosts
            PurchaseNoteTotal = cast(round(dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100, 0) as int),
            -- PurchaseNoteTotal / TotalSqft
            PurchaseCostSqft = cast(round((dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoney(p.TotalSqft), 1), 2) as decimal(18, 2)),
			cast(dbo.fnCastAsMoney(m.EstimatedSale) as decimal(18,0)) as EstimatedSale,
			-- EstimatedSale / TotalSqft
            SaleCostSqft = cast(round(dbo.fnCastAsMoney(m.EstimatedSale) / ISNULL(dbo.fnCastAsMoney(p.TotalSqft), 1), 2) as decimal(18, 2)),
            -- (NetDemandToEscrow - PurchaseNoteTotal)*.15 + EstimatedSale * @percentAA1 / 100
            CostOfSale = cast(round(((dbo.fnCastAsMoney(m.EstimatedSale) - dbo.fnCastAsMoney(m.EstimatedSale) * @percentAA1 / 100) - 
								(dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100)) * dbo.fnGetCostOfSalePercent(m.FeePercent) +
								dbo.fnCastAsMoney(m.EstimatedSale) * @percentAA1 / 100, 0) as int),
								-- EstimatedSale - EstimatedSale * @percentAA1%
			NetDemandToEscrow = cast(round(dbo.fnCastAsMoney(m.EstimatedSale) - dbo.fnCastAsMoney(m.EstimatedSale) * @percentAA1 / 100, 0) as int),
						
			-- ([Net Demand to escrow] - PurchaseNoteTotal) * 0.8
			NetProfit = cast(round(((dbo.fnCastAsMoney(m.EstimatedSale) - dbo.fnCastAsMoney(m.EstimatedSale) * @percentAA1 / 100) - 
									(dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100)) * dbo.fnGetNetProfitPercent(m.FeePercent), 0) as int),
			-- NetProfit / PurchaseNoteTotal
			PercentOfProfitVsPurchase = cast(cast(round(100*(((dbo.fnCastAsMoney(m.EstimatedSale) - dbo.fnCastAsMoney(m.EstimatedSale) * @percentAA1 / 100) - 
															(dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100)) * .8 / 
															(dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100)), 1) as decimal(18, 1)) as varchar) + '%',
			-- PurchaseNoteTotal / EstimatedSale
			CostToARV = cast(cast(round(100*((dbo.fnCastAsMoney(m.PurchasePrice) + dbo.fnCastAsMoney(m.RehabCosts) + dbo.fnCastAsMoney(m.PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoney(m.EstimatedSale), 1)), 1) as decimal(18, 1)) as varchar) + '%',
			-- CloseDate - StartDate
			TotalDays = datediff(DAY, dbo.fnCastAsDate(m.StartDate), dbo.fnCastAsDate(m.CloseDate)),
			case when isnull(m.FeePercent, '') = '' then '20' else m.FeePercent end as FeePercent,
			
			
			--3
			m.PurchaseAgreementReceived, m.CAP, m.TitleCompanyFront, m.Loan, m.LoanInfo,
			m.EffectiveDateBank, m.ECOE_F, m.AdjPurchasePrice, m.RECORDED_F,
			m.CommentsFront, m.ListingAgreement, m.ContractDate, m.ECOE_B,
			m.Recorded_B, m.BuyerPurchasePrice, m.PurchaseType, m.Incentive, m.HOAManagement,
			m.MonthlyHOADues, m.PastDueHOA, m.ResalePacketOrdered, m.ResalePkgToAgent, m.TitleCompanyBack,
			m.BuyerName, m.COOPAgent, m.Lender, m.WalkThrough, m.DocsIn, m.SellerSign,
			m.BuyerSign, m.CommentsBack, m.PropertyId,
			
			
			--1-2
			dbo.fnCastAsDate(m.CloseDate) as CloseDate, p.Stories, p.ConstructYear, p.TotalSqft, p.Bedrooms, p.Bathrooms, p.GarageSqft,
			p.Pool, p.Spa, 
			cast(dbo.fnCastAsMoney(m.PurchasePrice) as decimal(18,0)) as PurchasePrice,
			cast(dbo.fnCastAsMoney(m.RehabCosts) as decimal(18,0)) as RehabCosts,
			
			--2-3
			dbo.fnCastAsDate(m.DateAccept) as DateAccept,
			
			-- 4 - Rehab grid
			m.RehabStatus,
			m.ActualCompDate,
			m.NotesStatus, m.NotesStatusDate, m.NotesStatusUser,
			m.RehabStartDate,
			pm.Fname + ' ' + pm.LName as ProjectManager,
			m.EstRehabCompDate,
			CurrentRehabCosts = RehabMiscCosts + RehabEstimate,
			RehabEstimate,
			RehabMiscCosts
			
			
	FROM (SELECT * 
			FROM vwLvReo_MasterList 
			WHERE Apn LIKE '%'+@Apn+'%'
				AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
				AND isnull(ZipCode, '') LIKE '%'+@Zip+'%') AS m LEFT JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
																LEFT JOIN  UserAccounts ua on m.InvestorId = ua.UserId
																LEFT JOIN UserAccounts pm on m.PMId = pm.UserId
	--ORDER BY ISNULL(m.ECOE_B, @maxDate), ISNULL(m.ECOE_F, @maxDate), m.PropertyLocation
	order by PrimaryOrder, SecondaryOrder
			
			
			
			
END
go
 ALTER PROCEDURE [dbo].[getLvreo_MasterList]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0,
    @PMId int = null
)

AS

BEGIN
          DECLARE @maxInt INT
          SET @maxInt = 2147483647

        SELECT m.PropertyId, m.ListingStatus, dbo.fnCastAsDateTime(m.AuctionDate) AS AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
               m.PropertyLocation, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, dbo.fnCastAsDate(m.DateRecorded) as DateRecorded,
               p.LotSize, p.ConstructYear, p.LandUse, p.TotalSqft, p.GarageSqft,p.Stories, p.Bedrooms, p.Bathrooms,
               p.Pool, p.Spa, REPLACE(p.AssessorDesc, '<br/>', ' ') AS AssessorDesc, m.Phase,
               Client, dbo.fnCastAsDate(m.CloseDate) as CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source,
               ua.Fname + ' ' + ua.LName as InvestorName, InvestorId,
               dbo.fnCastAsDate(m.DateAccept) as DateAccept, m.EstimateSalePrice, m.EstimatedResale, m.NetSheet_SellingPrice, m.RECORDED_F, m.Recorded_B
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE (Apn LIKE '%'+@Apn+'%')
              AND (isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%')
              AND (isnull(ZipCode, '') LIKE '%'+@Zip+'%')
              AND (isnull(PMId, -1) BETWEEN ISNULL(@PMId, -1) AND ISNULL(@PMId, @maxInt)))  AS m 
          LEFT JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
        LEFT JOIN UserAccounts ua on m.InvestorId = ua.UserId
        where
               case
                    when @showAllUnassigned = 0 then 1
                    when (@showAllUnassigned = 1) and (ListingStatus = 'Unassigned') and (isnull(CloseDate, '') = '') then 1
                    else 0
               end = 1
        ORDER BY
               case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end

END
go
ALTER PROCEDURE [dbo].[getLvreo_MasterList_ListingStatusCount]
	@statusName varchar(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    Select count(ListingStatus)
    from vwLvReo_MasterList
    where ListingStatus like '%' + @statusName + '%'
END
go
ALTER PROCEDURE [dbo].[getLvreo_MasterListEscrow]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '' --Zip
    ,@FrontendReport bit = 0
    ,@BackendReport bit = 0
    ,@archive bit = 0
)

AS

BEGIN
	declare @5daysVariable int
	declare @5daysVariableStr varchar(max)
	
	set @5daysVariableStr = (select Value from Config where [Key] = 'Escrow10daysVariable')
	if isnumeric(@5daysVariableStr) = 1 set @5daysVariable = CAST(@5daysVariableStr as int)
	else set @5daysVariable = 10

	SELECT
		PurchaseAgreementReceived
		,dbo.fnCastAsDate(CAP) as CAP
		,dbo.fnCastAsDate(DateAccept) as DateAccept
		,m.Apn
		,m.PropertyLocation
		,m.ZipCode		
		,ua.Fname + ' ' + ua.LName as InvestorName
		,TitleCompanyFront
		,Loan
		,LoanInfo
		,EffectiveDateBank
		,ECOE_F
		,case when isnumeric(PurchasePrice) = 1 then PurchasePrice else '0' end as PurchasePrice
		,AdjPurchasePrice
		,RECORDED_F
		,CommentsFront
		,'' as [Space]
		,ListingAgreement
		,ContractDate
		,ECOE_B
		,Recorded_B
		,dbo.fnCastAsMoneyOrZero(BuyerPurchasePrice) as BuyerPurchasePrice
		,PurchaseType
		,Incentive
		,HOAManagement
		,MonthlyHOADues
		,PastDueHOA
		,ResalePacketOrdered
		,ResalePkgToAgent
		,TitleCompanyBack
		,BuyerName
		,COOPAgent
		,Lender
		,WalkThrough
		,DocsIn
		,SellerSign
		,BuyerSign
		,CommentsBack
		,m.PropertyId
		,CloseDate
		,NetProfit
		,Bedrooms
		,Bathrooms
		,ConstructYear
		,GarageSqft
		,[Pool]
		,TotalSqft
		,case
			-- Purple - when Recorded-B date is entered
			when Recorded_B is not null then 'PastelPurple'
		
			-- Orange - ECOE-B within the variable days or past
			when (ECOE_B IS not null) and (GETDATE() > ECOE_B or abs(DATEDIFF(day, GETDATE(), ECOE_B)) <= @5daysVariable) then 'Orange'

			-- Yellow - when ECOE-B date is entered but not within or past variable days
			when (ECOE_B IS not null) and GETDATE() < ECOE_B and (abs(DATEDIFF(day, GETDATE(), ECOE_B)) > @5daysVariable) then 'Yellow'
			
			-- No Color - no date entered on ECOE-B
			when ECOE_B IS null then null			
		end as Color
		,case
			when dbo.fnCastAsMoney(RehabCosts) is not null then cast(dbo.fnCastAsMoney(RehabCosts) as decimal(18,0))
			else 0 end as RehabCosts
	into #tmp
	FROM (SELECT * 
		  FROM vwLvReo_MasterList 
		  WHERE Apn LIKE '%'+isnull(@Apn,'')+'%'
		  AND isnull(PropertyLocation, '') LIKE '%'+isnull(@PropLocation,'')+'%'
		  AND isnull(ZipCode, '') LIKE '%'+isnull(@Zip,'')+'%')  AS m
	 LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
	 left join UserAccounts ua on m.InvestorId = ua.UserId
	where
		/*(@FrontendReport = 0 or (ECOE_F is null))
		and (@BackendReport = 0 or ((ECOE_F is not null) and (RECORDED_B is null)))
		and*/ (@archive = 0 or (RECORDED_B is not null))
	order by
		case
			-- Purple - when Recorded-B date is entered
			when Recorded_B is not null then 3
		
			-- Orange - ECOE-B within the variable days or past
			when (ECOE_B IS not null) and (GETDATE() > ECOE_B or abs(DATEDIFF(day, GETDATE(), ECOE_B)) <= @5daysVariable) then 0

			-- Yellow - when ECOE-B date is entered but not within or past variable days
			when (ECOE_B IS not null) and GETDATE() < ECOE_B and (abs(DATEDIFF(day, GETDATE(), ECOE_B)) > @5daysVariable) then 1

			-- No Color - no date entered on ECOE-B
			when ECOE_B IS null then 2
		end
		
	if @FrontendReport = 1
		select *
		from #tmp
		order by
			case
				when Recorded_B is null then DateAccept
				else Recorded_B end desc
	else
		select *
		from #tmp
END
go
ALTER PROCEDURE [dbo].[getLvreo_MasterListEx]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0
)

AS

BEGIN

declare @percentT1 money
declare @percentAA1 money
set @percentT1 = (select Value from Config where [Key] = 'PercentT1')
set @percentAA1 = (select Value from Config where [Key] = 'PercentAA1')

        SELECT
			ua.Fname + ' ' + ua.LName as InvestorName,
			EMD,
			Reimb,
			Insur,
			Assmts,
			dbo.fnCastAsDate(m.DateAccept) as DateAccept,
			m.Apn,
			m.PropertyLocation,
			m.ZipCode,
			dbo.fnCastAsDate(StartDate) as StartDate,
			p.Stories,
			p.ConstructYear,
			p.TotalSqft,			
			p.Bedrooms,
			p.Bathrooms,
			p.GarageSqft,
			p.Pool,
			p.Spa,
			cast(dbo.fnCastAsMoneyOrZero(PurchasePrice) as decimal(18,0)) as PurchasePrice
			,ClosingCost = cast(round(dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100, 0) as int)
			,cast(dbo.fnCastAsMoneyOrZero(RehabCosts) as decimal(18,0)) as RehabCosts
			-- PurchasePrice + ClosingCost + RehabCosts
            ,PurchaseNoteTotal =
				cast(round(dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100, 0) as int)		
           -- PurchaseNoteTotal / TotalSqft
           ,PurchaseCostSqft =
				case when dbo.fnCastAsMoneyOrZero(TotalSqft) <> 0 then
					cast(round((dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoneyOrZero(TotalSqft), 1), 2) as decimal(18, 2))
				else 0 end
			,cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)) as EstimatedSale
			-- EstimatedSale / TotalSqft
           ,SaleCostSqft =
				case when dbo.fnCastAsMoneyOrZero(TotalSqft) <> 0 then 
					cast(round(dbo.fnCastAsMoneyOrZero(EstimatedSale) / ISNULL(dbo.fnCastAsMoneyOrZero(TotalSqft), 1), 2) as decimal(18, 2))
				else 0 end
           -- (NetDemandToEscrow - PurchaseNoteTotal)*.15 + EstimatedSale * @percentAA1 / 100
           ,CostOfSale =
				cast(round(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100) - 
				(dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100))*dbo.fnGetCostOfSalePercent(FeePercent)
				+
				dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100, 0) as int)
			,AA = ''
			-- EstimatedSale - EstimatedSale * @percentAA1%
			,NetDemandToEscrow =
				cast(round(dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100, 0) as int)
			-- ([Net Demand to escrow] - PurchaseNoteTotal) * 0.8
			,NetProfit =
				cast(round(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100)
					- (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)) * dbo.fnGetNetProfitPercent(FeePercent), 0) as int)
			-- NetProfit / PurchaseNoteTotal
			,PercentOfProfitVsPurchase =
				case when dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 <> 0 then
					cast(cast(round(100*(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100) - (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)) * .8 / (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)), 1) as decimal(18, 1)) as varchar) + '%'
				else '0%' end	
			-- PurchaseNoteTotal / EstimatedSale
			,CostToARV =
				case when ISNULL(dbo.fnCastAsMoneyOrZero(EstimatedSale), 1) <> 0 then
					cast(cast(round(100*((dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoneyOrZero(EstimatedSale), 1)), 1) as decimal(18, 1)) as varchar) + '%'
				else '0%' end
			,dbo.fnCastAsDate(CloseDate) as CloseDate
			,[# Days] = ''
			-- CloseDate - StartDate
			,TotalDays = datediff(DAY, dbo.fnCastAsDate(StartDate), dbo.fnCastAsDate(CloseDate))
			, Recorded_B
        ,m.PropertyId, m.ListingStatus, dbo.fnCastAsDateTime(m.AuctionDate) AS AuctionDate, m.TsNumber,   m.Trustee, m.Site, 
                m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, dbo.fnCastAsDate(m.DateRecorded) as DateRecorded,
               p.LotSize,  p.LandUse, 
                 p.AssessorDesc, m.Phase,
               Client,  EscrowCompany, MiscNotes, PrelistStatus,   Source,
                InvestorId, case when isnull(FeePercent, '') = '' then '20' else FeePercent end as FeePercent
                
			,cast(NetProfit as int) as NetProfit2
			,Bedrooms
			,Bathrooms
			,ConstructYear
			,GarageSqft
			,[Pool]
			,TotalSqft,
			m.DeedRestrictions, m.RehabMiscCosts + m.RehabEstimate AS CurrentRehabCosts, 
			m.NetSheet_SellingPrice AS ActualSale, 
			convert(int, m.NetSheet_SellingPrice - ISNULL(CONVERT(money, m.EstimatedSale), 0)) AS ActualvsEstimate,
			convert(varchar, case when cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)) <> 0 then
				convert(int, (m.NetSheet_SellingPrice - ISNULL(CONVERT(money, m.EstimatedSale), 0)) * 100 / cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)))
			else 0
			end) + '%' as ActualvsEstimatePercent,
			m.[Type]
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
              AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
              AND isnull(ZipCode, '') LIKE '%'+@Zip+'%')  AS m          
         LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
         left join UserAccounts ua on m.InvestorId = ua.UserId
        where
			case
				when @showAllUnassigned = 0 then 1
				when (@showAllUnassigned = 1) and (ListingStatus = 'Unassigned') and (isnull(CloseDate, '') = '') then 1
				else 0
			end = 1
        ORDER BY
			case when ISNULL(Recorded_B, '') = '' then m.ZipCode else 'z' end

END
go
ALTER PROCEDURE [dbo].[getLvreo_PropertyInfo]
(
    @propertyId int = null,
    @apn varchar(100) = null
)

AS
BEGIN
	select
		rtrim(ltrim(isnull(PropertyLocation, ''))) + ', ' + rtrim(ltrim(isnull(ZipCode, ''))) as Location,
		rtrim(ltrim(isnull(MiscNotes, ''))) as PropertyDetails,
		Apn
	from vwLvreo_MasterList
	where PropertyId = isnull(@propertyId, 0) or (Apn = isnull(@apn, ''))
END
go
ALTER PROCEDURE [dbo].[getMyInvestments]
	@currentUserId int,
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '' --Zip	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    declare @userSubRole char(1)
	set @userSubRole = (select [SubRole] from UserAccounts where UserId = @currentUserId)
	
	if @userSubRole = 'L'
	begin
        SELECT m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
               m.PropertyLocation, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
               p.LotSize, p.ConstructYear, p.LandUse, p.TotalSqft, p.GarageSqft,p.Stories, p.Bedrooms, p.Bathrooms,
               p.Pool, p.Spa, p.AssessorDesc, m.Phase,
               Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
				  AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
				  AND isnull(ZipCode, '') LIKE '%'+@Zip+'%'
				  and InvestorId = @currentUserId)  AS m 
         LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
        ORDER BY m.ZipCode
	end
	else if @userSubRole = 'S'
	begin
		SELECT m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
			   m.PropertyLocation, m.LotSize, m.ConstructYear, m.LandUse, m.TotalSqft, m.GarageSqft,m.Stories, m.Bedrooms, 
			   m.Bathrooms, m.Pool, m.Spa, m.AssessorDesc, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, 
			   m.MaxBid, m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
			   m.Phase,
			   Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source
		FROM Sdreo_MasterList m
		WHERE Apn LIKE '%'+@Apn+'%' 
		  AND PropertyLocation LIKE '%'+@PropLocation+'%'
		  AND ZipCode LIKE '%'+@Zip+'%'
		  and InvestorId = @currentUserId
		ORDER BY m.ZipCode
	end
END
go
ALTER PROCEDURE [dbo].[getNetSheet]
(
	@PropertyId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT Apn,
		dbo.fnCastAsMoneyOrZero(BuyerPurchasePrice) as BuyerPurchasePrice,
		dbo.fnCastAsMoneyOrZero(PurchasePrice) as PurchasePrice,
		dbo.fnCastAsMoneyOrZero(RehabCosts) as RehabCosts,
		NetSheet_LockChange_Estimated, NetSheet_LockChange_Actual, NetSheet_LockChange_WithLoan, 
		NetSheet_ReserveForUtilites_Estimated, NetSheet_ReserveForUtilites_Actual, NetSheet_ReserveForUtilites_WithLoan, 
		NetSheet_Insurance_Estimated, NetSheet_Insurance_Actual, NetSheet_Insurance_WithLoan, 
		NetSheet_CashForKeys_Estimated, NetSheet_CashForKeys_Actual, NetSheet_CashForKeys_WithLoan, 
		NetSheet_Miscellaenous_Estimated, NetSheet_Miscellaenous_Actual, NetSheet_Miscellaenous_WithLoan, 
		NetSheet_BrokerCoop, NetSheet_SellerClosingIncentives, NetSheet_EscrowClosingEstimate, 
		NetSheet_SellersCostRepairs_Estimated, NetSheet_SellersCostRepairs_Actual, NetSheet_SellersCostRepairs_WithLoan, 
		NetSheet_HomeWarranty_Estimated, NetSheet_HomeWarranty_Actual, NetSheet_HomeWarranty_WithLoan, 
		NetSheet_Appraisal_Estimated, NetSheet_Appraisal_Actual, NetSheet_Appraisal_WithLoan, 
		NetSheet_MiscellaneousClosingCosts_Estimated, NetSheet_MiscellaneousClosingCosts_Actual, NetSheet_MiscellaneousClosingCosts_WithLoan, 
		NetSheet_HOADues_Estimated, NetSheet_HOADues_Actual, NetSheet_HOADues_WithLoan, 
		NetSheet_TransactionFee_Estimated, NetSheet_TransactionFee_Actual, NetSheet_TransactionFee_WithLoan, 
		NetSheet_Other_Estimated, NetSheet_Other_Actual, NetSheet_Other_WithLoan, 
		NetSheet_FeeLCH,
		
		NetSheet_NonProfitFee, NetSheet_ClosingCosts, 
		NetSheet_LoanBrokerFee, NetSheet_LoanFee, NetSheet_LoanInterest,
		
		NetSheet_RehabCost_Estimated, NetSheet_RehabCost_Actual, NetSheet_RehabCost_WithLoan,
		NetSheet_1stDOT, NetSheet_DownPayment,
		
		NetSheet_SellingPrice, NetSheet_MortgageType, NetSheet_EstClosingDate,
		NetSheet_WithLoan,
		
		NetSheet_ClosingCosts_Estimated, NetSheet_ClosingCosts_Actual, NetSheet_ClosingCosts_WithLoan,
		NetSheet_Manual_ClosingCosts_Estimated, NetSheet_Manual_ClosingCosts_Actual, NetSheet_Manual_ClosingCosts_WithLoan,
		NetSheet_Occupancy, NetSheet_AcquisitionDate,
		NetSheet_NonProfitFee_Estimated, NetSheet_NonProfitFee_Actual, NetSheet_NonProfitFee_WithLoan, NetSheet_Manual_NonProfitFee_Estimated, NetSheet_Manual_NonProfitFee_Actual, NetSheet_Manual_NonProfitFee_WithLoan, 
		NetSheet_Broker_Estimated, NetSheet_Broker_Actual, NetSheet_Broker_WithLoan, NetSheet_Manual_Broker_Estimated, NetSheet_Manual_Broker_Actual, NetSheet_Manual_Broker_WithLoan, 
		NetSheet_SellerClosing_Estimated, NetSheet_SellerClosing_Actual, NetSheet_SellerClosing_WithLoan, NetSheet_Manual_SellerClosing_Estimated, NetSheet_Manual_SellerClosing_Actual, NetSheet_Manual_SellerClosing_WithLoan, 
		NetSheet_Escrow_Estimated, NetSheet_Escrow_Actual, NetSheet_Escrow_WithLoan, NetSheet_Manual_Escrow_Estimated, NetSheet_Manual_Escrow_Actual, NetSheet_Manual_Escrow_WithLoan,
		NetSheet_ProfitParticipation_Estimated, NetSheet_ProfitParticipation_Actual, NetSheet_ProfitParticipation_WithLoan, NetSheet_Manual_ProfitParticipation_Estimated, NetSheet_Manual_ProfitParticipation_Actual, NetSheet_Manual_ProfitParticipation_WithLoan, 
		NetSheet_LoanFeeSumma, NetSheet_Manual_LoanFee,
		NetSheet_LoanInterestSumma, NetSheet_Manual_LoanInterest,
		
		NetSheet_PurchasePrice_Actual, NetSheet_Manual_PurchasePrice_Actual, NetSheet_PurchasePrice_WithLoan, NetSheet_Manual_PurchasePrice_WithLoan, 
		NetSheet_Total_AcquisitionCost_Estimated, NetSheet_Manual_Total_AcquisitionCost_Estimated, NetSheet_Total_AcquisitionCost_Actual, NetSheet_Manual_Total_AcquisitionCost_Actual, NetSheet_Total_AcquisitionCost_WithLoan, NetSheet_Manual_Total_AcquisitionCost_WithLoan, 
		NetSheet_Total_PreparationCost_Estimated, NetSheet_Manual_Total_PreparationCost_Estimated, NetSheet_Total_PreparationCost_Actual, NetSheet_Manual_Total_PreparationCost_Actual, NetSheet_Total_PreparationCost_WithLoan, NetSheet_Manual_Total_PreparationCost_WithLoan, 
		NetSheet_Total_PresaleCost_Estimated, NetSheet_Manual_Total_PresaleCost_Estimated, NetSheet_Total_PresaleCost_Actual, NetSheet_Manual_Total_PresaleCost_Actual, NetSheet_Total_PresaleCost_WithLoan, NetSheet_Manual_Total_PresaleCost_WithLoan, 
		NetSheet_SellingPrice_Estimated, NetSheet_Manual_SellingPrice_Estimated, NetSheet_SellingPrice_Actual, NetSheet_Manual_SellingPrice_Actual, NetSheet_SellingPrice_WithLoan, NetSheet_Manual_SellingPrice_WithLoan, 
		NetSheet_Total_ClosingCost_Estimated, NetSheet_Manual_Total_ClosingCost_Estimated, NetSheet_Total_ClosingCost_Actual, NetSheet_Manual_Total_ClosingCost_Actual, NetSheet_Total_ClosingCost_WithLoan, NetSheet_Manual_Total_ClosingCost_WithLoan, 
		NetSheet_Note, NetSheet_Manual_Note, NetSheet_2ndDOT, NetSheet_Manual_2ndDOT, NetSheet_Total_LoanCost, NetSheet_Manual_Total_LoanCost, NetSheet_NetProfit_Estimated, 
		NetSheet_Manual_NetProfit_Estimated, NetSheet_NetProfit_Actual, NetSheet_Manual_NetProfit_Actual, NetSheet_NetProfit_WithLoan, NetSheet_Manual_NetProfit_WithLoan, 
		NetSheet_Total_Equity_Estimated, NetSheet_Manual_Total_Equity_Estimated, NetSheet_Total_Equity_Actual, NetSheet_Manual_Total_Equity_Actual, NetSheet_Total_Equity_WithLoan, NetSheet_Manual_Total_Equity_WithLoan, 
		NetSheet_Total_NetProfit_Estimated, NetSheet_Manual_Total_NetProfit_Estimated, NetSheet_Total_NetProfit_Actual, NetSheet_Manual_Total_NetProfit_Actual, NetSheet_Total_NetProfit_WithLoan, NetSheet_Manual_Total_NetProfit_WithLoan, 
		NetSheet_Total_NetPercent_Estimated, NetSheet_Manual_Total_NetPercent_Estimated, NetSheet_Total_NetPercent_Actual, NetSheet_Manual_Total_NetPercent_Actual, NetSheet_Total_NetPercent_WithLoan, NetSheet_Manual_Total_NetPercent_WithLoan
	FROM vwLvReo_MasterList
	WHERE PropertyId = @PropertyId		
END
go
ALTER PROCEDURE [dbo].[getProjectManagerList]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '' --Zip
)
AS
BEGIN
        SELECT m.PropertyId, ua.Fname + ' ' + ua.LName as InvestorName, m.PropertyLocation, m.StartDate, m.Apn, m.ZipCode
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
              AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
              AND isnull(ZipCode, '') LIKE '%'+@Zip+'%')  AS m          
         LEFT JOIN UserAccounts ua on m.InvestorId = ua.UserId
        ORDER BY case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end
END
go
ALTER PROCEDURE [dbo].[getPropertyCombo]
AS
BEGIN
	SELECT PropertyId, PropertyLocation
	FROM vwLvReo_MasterList
	ORDER BY PropertyLocation
END
go
ALTER PROCEDURE [dbo].[getRehab]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @PMId int = null
)
AS
BEGIN
	declare @5daysVariable int
	declare @5daysVariableStr varchar(max)
	
	set @5daysVariableStr = (select Value from Config where [Key] = '5daysVariable')
	if isnumeric(@5daysVariableStr) = 1 set @5daysVariable = CAST(@5daysVariableStr as int)
	else set @5daysVariable = 5


		DECLARE @maxInt INT
		SET @maxInt = 2147483647
		
        SELECT m.PropertyId, invest.Fname + ' ' + invest.LName as InvestorName, m.PropertyLocation,
			cast(m.StartDate as datetime) as StartDate,
			m.Apn, m.ZipCode,
			m.RehabStatus, m.ActualCompDate, m.RehabStartDate, m.PMId,
			m.NotesStatus, m.NotesStatusDate, m.NotesStatusUser,
			ua.Fname + ' ' + ua.LName as ProjectManager, m.EstRehabCompDate,
			CurrentRehabCosts = RehabMiscCosts + RehabEstimate,
			RehabEstimate,
			RehabMiscCosts,
			case
				when (EstRehabCompDate is not null)
					and (abs(DATEDIFF(day, getdate(), EstRehabCompDate)) <= @5daysVariable) then 'Pink'
				when (ActualCompDate is not null) then 'MediumPurple'
				when (PMId is not null) then 'Yellow'
				else 'White'
			end as BackColor,
			
			case
				when (EstRehabCompDate is not null)
					and (abs(DATEDIFF(day, getdate(), EstRehabCompDate)) <= @5daysVariable) then 1
				when (ActualCompDate is not null) then 4
				when (PMId is not null) then 2
				else 3
			end as SortOrder,
			
			p.Stories, p.ConstructYear, p.TotalSqft, p.LotSize, p.Bedrooms, p.Bathrooms, p.GarageSqft, p.Pool, p.Spa
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE (Apn LIKE '%'+@Apn+'%')
              AND (isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%')
              AND (isnull(ZipCode, '') LIKE '%'+@Zip+'%')
              AND (isnull(PMId, -1) BETWEEN ISNULL(@PMId, -1) AND ISNULL(@PMId, @maxInt)))  AS m          
         LEFT JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
         LEFT JOIN UserAccounts ua on m.PMId = ua.UserId
         left join UserAccounts invest on m.InvestorId = invest.UserId
        ORDER by SortOrder
			/*case
				when (EstRehabCompDate is not null)
					and (abs(DATEDIFF(day, getdate(), EstRehabCompDate)) <= @5daysVariable) then 1
				-- when  Project Scope Form is approved then (sort order 2)
				when (PMId is not null) then 3				
				when (PMId is null) then 4
				else 4
			end*/
END
go
ALTER PROCEDURE [dbo].[getRehabStatusCodes]
AS
BEGIN
	SELECT RehabStatusCodesId, Code, Description, cast(case when l.RehabStatus IS null then 0 else 1 end as bit) as HasProperties
	FROM RehabStatusCodes 
		left join
			(select distinct RehabStatus
			from vwLvReo_MasterList) l on Code = l.RehabStatus
	ORDER BY Code
END
go